/*
 * Copyright (c) 2006 Monsur Hossain

 * Permission is hereby granted, free of charge, to any person obtaining a 
 * copy of this software and associated documentation files (the 
 * "Software"), to deal in the Software without restriction, including 
 * without limitation the rights to use, copy, modify, merge, publish, 
 * distribute, sublicense, and/or sell copies of the Software, and to 
 * permit persons to whom the Software is furnished to do so, subject to 
 * the following conditions:

 * The above copyright notice and this permission notice shall be included 
 * in all copies or substantial portions of the Software.

 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS 
 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF 
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. 
 * IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY 
 * CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, 
 * TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE 
 * SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */
using System;
using System.Data;
using System.Collections.Generic;
using Finisar.SQLite;

namespace RhapsodyNET
{
    public class RhapsodyDB
    {
        // connection string for Rhapsody's SQLite database
        private string connectionString;

        public RhapsodyDB(string dbFile)
        {
            // generate the connection string based off the path
            // to the SQLite database
            connectionString = "Data Source=" + dbFile + ";";
        }

        /// <summary>
        /// Retrieves the last track played by Rhapsody
        /// </summary>
        public RhapsodyTrack GetLastPlayedTrack()
        {
            RhapsodyTrack track = null;

            using (SQLiteConnection sqliteConn = new SQLiteConnection(connectionString))
            {
                SQLiteCommand sqliteCmd = new SQLiteCommand("select * from track order by last_played desc limit 1;", sqliteConn);

                sqliteConn.Open();
                using (SQLiteDataReader sdr = sqliteCmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (sdr.Read())
                    {
                        track = PopulateTrackObject(sdr);
                    }
                    sdr.Close();
                }
                sqliteConn.Close();
            }

            return track;
        }

        /// <summary>
        /// Retrieves all the tracks played by Rhapsody since a certain date
        /// </summary>
        public IList<RhapsodyTrack> GetLatestTracks(DateTime since)
        {
            IList<RhapsodyTrack> tracks = new List<RhapsodyTrack>();

            using (SQLiteConnection sqliteConn = new SQLiteConnection(connectionString))
            {
                SQLiteCommand sqliteCmd = new SQLiteCommand("SELECT * FROM TRACK WHERE LAST_PLAYED > '" + SqliteHelper.DateToString(since) + "' ORDER BY LAST_PLAYED ASC", sqliteConn);

                sqliteConn.Open();
                using (SQLiteDataReader sdr = sqliteCmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (sdr.Read())
                    {
                        RhapsodyTrack track = PopulateTrackObject(sdr);

                        tracks.Add(track);
                    }
                    sdr.Close();
                }
                sqliteConn.Close();
            }

            return tracks;
        }

        /// <summary>
        /// Populte a Track object based off a single row in the SqlDataReader
        /// We only grab a few necessary fields for now, but this can be 
        /// expanded to grab the whole set of fields listed below.
        /// </summary>
        private static RhapsodyTrack PopulateTrackObject(SQLiteDataReader sdr)
        {
            RhapsodyTrack track = new RhapsodyTrack();

            track.TrackID = sdr.GetInt32(0);
            track.DateAdded = SqliteHelper.StringToDate(sdr.GetString(4));
            track.LastModified = SqliteHelper.StringToDate(sdr.GetString(5));
            track.LastPlayed = SqliteHelper.StringToDate(sdr.GetString(6));
            track.PlayCount = sdr.GetInt32(7);
            track.ArtistName = sdr.GetString(15);
            track.AlbumName = sdr.GetString(20);
            track.TrackName = sdr.GetString(24);
            track.TrackLength = sdr.GetInt32(31);
            return track;
        }
    }
}
